-- @owner: @li-xin12345
-- @date: 2023/8/3
-- @testpoint: 分区表/二级分区表，非压缩表，修改compresstype=2, compress_level不同取值，部分步骤合理报错
-- @history: Modified by li-xin12345 2025.2.12：研发代码变更，取消参数控制，适配用例

--step1:创建函数查询表文件相对路径;expect:成功
drop function if exists compress_func_findpath(character varying);
create or replace function compress_func_findpath(character varying)
  returns character varying
  language plpgsql
as
$body$
declare
  relpath character varying;
begin
  relpath = (select pg_relation_filepath(relname::regclass) from pg_class where relname =  $1);
  return relpath;
end;
$body$;
/

--step2:建分区表并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0032_01 cascade;
create table t_alter_rowcompress_0032_01 (col1 int, col2 int)
partition by list(col1)
(
partition p1 values (2000),
partition p2 values (3000),
partition p3 values (4000),
partition p4 values (5000)
);

insert into t_alter_rowcompress_0032_01 values(2000, generate_series(0, 200));
insert into t_alter_rowcompress_0032_01 values(3000, generate_series(0, 300));
insert into t_alter_rowcompress_0032_01 values(4000, generate_series(0, 400));
insert into t_alter_rowcompress_0032_01 values(5000, generate_series(0, 500));
select relname, reloptions from pg_partition order by relname;

--step3:分区表修改压缩参数compresstype=2,compress_level=-31,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0032_01 set (compresstype=2,compress_level=-31);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0032_01'));

--step4:分区表修改压缩参数compresstype=2,compress_level=1,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0032_01 set (compresstype=2,compress_level=1);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0032_01'));

--step5:分区表修改压缩参数compresstype=2,compress_level=31,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0032_01 set (compresstype=2,compress_level=31);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0032_01'));

--step6:分区表修改压缩参数compresstype=2,compress_level为取值范围外值;expect:合理报错
alter table t_alter_rowcompress_0032_01 set (compresstype=2,compress_level=99);
alter table t_alter_rowcompress_0032_01 set (compresstype=2,compress_level=-99);
select relname, reloptions from pg_partition order by relname;

--step7:分区表修改压缩参数compresstype=2,compress_level为无效值;expect:合理报错
alter table t_alter_rowcompress_0032_01 set (compresstype=2,compress_level='test');
alter table t_alter_rowcompress_0032_01 set (compresstype=2,compress_level=2.2);
select relname, reloptions from pg_partition order by relname;

--step8:分区表清理分区表，建二级分区表;expect:成功
drop table if exists t_alter_rowcompress_0032_01 cascade;
drop table if exists t_alter_rowcompress_0032_02 cascade;
create table t_alter_rowcompress_0032_02
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
)
partition by range (month_code) subpartition by hash (dept_code)
(
  partition p_201901 values less than( '201903' )
  (
    subpartition p_201901_a,
    subpartition p_201901_b
  ),
  partition p_201902 values less than( '201904' )
  (
    subpartition p_201902_a,
    subpartition p_201902_b
  )
);
insert into t_alter_rowcompress_0032_02 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0032_02 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0032_02 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0032_02 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0032_02 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0032_02 values('201903', '2', '1', 1);
select relname, reloptions from pg_partition order by relname;

--step9:二级分区表修改压缩参数compresstype=2,compress_level=-31,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0032_02 set (compresstype=2,compress_level=-31);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0032_02'));

--step10:二级分区表修改压缩参数compresstype=2,compress_level=1,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0032_02 set (compresstype=2,compress_level=1);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0032_02'));

--step11:二级分区表修改压缩参数compresstype=2,compress_level=31,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0032_02 set (compresstype=2,compress_level=31);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0032_02'));

--step12:二级分区表修改压缩参数compresstype=2,compress_level为取值范围外值;expect:合理报错
alter table t_alter_rowcompress_0032_02 set (compresstype=2,compress_level=99);
alter table t_alter_rowcompress_0032_02 set (compresstype=2,compress_level=-99);
select relname, reloptions from pg_partition order by relname;

--step13:二级分区表修改压缩参数compresstype=2,compress_level为无效值;expect:合理报错
alter table t_alter_rowcompress_0032_02 set (compresstype=2,compress_level='test');
alter table t_alter_rowcompress_0032_02 set (compresstype=2,compress_level=2.2);
select relname, reloptions from pg_partition order by relname;

--step14:清理二级分区表;expect:成功
drop table if exists t_alter_rowcompress_0032_02 cascade;
drop function if exists compress_func_findpath(character varying);